<?php
// 数据库配置
define('DB_HOST', 'localhost');
define('DB_NAME', 'music_db');
define('DB_USER', '73327382');
define('DB_PASS', '73327382');

// 创建数据库连接
try {
    $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8";
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 修复表结构：确保id字段是自增主键
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS songs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            artist VARCHAR(255) NOT NULL,
            album VARCHAR(255) NOT NULL,
            mp3_url VARCHAR(255) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ");
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

// 处理表单提交
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['add_song'])) {
        $title = $_POST['title'] ?? '';
        $artist = $_POST['artist'] ?? '';
        $album = $_POST['album'] ?? '';
        $mp3_url = $_POST['mp3_url'] ?? '';
        
        if (!empty($title) && !empty($artist) && !empty($mp3_url) && !empty($album)) {
            $stmt = $pdo->prepare("INSERT INTO songs (title, artist, album, mp3_url) VALUES (?, ?, ?, ?)");
            $stmt->execute([$title, $artist, $album, $mp3_url]);
            $success = "歌曲添加成功!";
        }
    }
    elseif (isset($_POST['update_song'])) {
        $id = $_POST['id'] ?? 0;
        $title = $_POST['title'] ?? '';
        $artist = $_POST['artist'] ?? '';
        $album = $_POST['album'] ?? '';
        $mp3_url = $_POST['mp3_url'] ?? '';
        
        if ($id > 0 && !empty($title) && !empty($artist) && !empty($mp3_url) && !empty($album)) {
            $stmt = $pdo->prepare("UPDATE songs SET title = ?, artist = ?, album = ?, mp3_url = ? WHERE id = ?");
            $stmt->execute([$title, $artist, $album, $mp3_url, $id]);
            $success = "歌曲更新成功!";
        }
    }
}

// 处理删除请求
if (isset($_GET['delete'])) {
    $id = $_GET['delete'] ?? 0;
    if ($id > 0) {
        $stmt = $pdo->prepare("DELETE FROM songs WHERE id = ?");
        $stmt->execute([$id]);
        $success = "歌曲删除成功!";
    }
}

// 获取搜索关键词
$search = $_GET['search'] ?? '';

// 分页设置
$perPage = 10; // 每页显示的歌曲数量
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1; // 当前页码
$offset = ($page - 1) * $perPage; // 计算偏移量

// 获取歌曲总数（用于分页）
if (!empty($search)) {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM songs WHERE title LIKE ? OR artist LIKE ? OR album LIKE ?");
    $searchTerm = "%$search%";
    $stmt->execute([$searchTerm, $searchTerm, $searchTerm]);
    $totalSongs = $stmt->fetchColumn();
    
    $stmt = $pdo->prepare("SELECT * FROM songs WHERE title LIKE ? OR artist LIKE ? OR album LIKE ? LIMIT ? OFFSET ?");
    $stmt->bindValue(1, $searchTerm, PDO::PARAM_STR);
    $stmt->bindValue(2, $searchTerm, PDO::PARAM_STR);
    $stmt->bindValue(3, $searchTerm, PDO::PARAM_STR);
    $stmt->bindValue(4, $perPage, PDO::PARAM_INT);
    $stmt->bindValue(5, $offset, PDO::PARAM_INT);
    $stmt->execute();
} else {
    $totalSongs = $pdo->query("SELECT COUNT(*) FROM songs")->fetchColumn();
    
    $stmt = $pdo->prepare("SELECT * FROM songs LIMIT ? OFFSET ?");
    $stmt->bindValue(1, $perPage, PDO::PARAM_INT);
    $stmt->bindValue(2, $offset, PDO::PARAM_INT);
    $stmt->execute();
}

$songs = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 计算总页数
$totalPages = ceil($totalSongs / $perPage);

// 获取统计数据
$artists = $pdo->query("SELECT artist, COUNT(*) as count FROM songs GROUP BY artist")->fetchAll();
$mostPopularArtist = $pdo->query("SELECT artist, COUNT(*) as count FROM songs GROUP BY artist ORDER BY count DESC LIMIT 1")->fetch();

// 获取编辑歌曲ID
$edit_id = $_GET['edit'] ?? 0;
$edit_song = null;
if ($edit_id > 0) {
    $stmt = $pdo->prepare("SELECT * FROM songs WHERE id = ?");
    $stmt->execute([$edit_id]);
    $edit_song = $stmt->fetch(PDO::FETCH_ASSOC);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="zh-CN">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" />
    <title>音乐数据库管理系统</title>
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/es5-shim/4.5.7/es5-shim.min.js"></script>
    <![endif]-->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <style type="text/css">
        body {
            margin: 0;
            padding: 0;
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: #1d3557;
            color: #f1faee;
            min-height: 100vh;
            padding: 20px;
        }
        
        .container {
            width: 100%;
            max-width: 1200px;
            margin: 0 auto;
        }
        
        header {
            display: block;
            padding: 20px 0;
            margin-bottom: 20px;
            border-bottom: 1px solid #8d99ae;
            overflow: hidden;
        }
        
        .logo {
            float: left;
        }
        
        .logo .fa {
            font-size: 2.5rem;
            color: #4cc9f0;
            margin-right: 15px;
            float: left;
        }
        
        .logo h1 {
            font-size: 2.2rem;
            color: #4cc9f0;
            font-weight: 800;
            margin: 0;
            padding: 0;
            line-height: 2.5rem;
        }
        
        .btn {
            padding: 10px 15px;
            border: none;
            font-weight: 600;
            cursor: pointer;
            display: inline-block;
            text-decoration: none;
            font-size: 1rem;
            color: white;
            margin: 5px;
        }
        
        .btn-primary {
            background: #4361ee;
        }
        
        .btn-success {
            background: #4cc9f0;
        }
        
        .btn-danger {
            background: #f72585;
        }
        
        .card {
            background: #2b2d42;
            border: 1px solid #8d99ae;
            padding: 15px;
            margin-bottom: 20px;
            float: left;
            width: 30%;
            margin-right: 3%;
            box-sizing: border-box;
        }
        
        .card h3 {
            font-size: 1.3rem;
            margin-bottom: 15px;
            color: #4cc9f0;
        }
        
        .card .stat {
            font-size: 2.5rem;
            font-weight: 700;
            margin: 10px 0;
            color: #4cc9f0;
        }
        
        .artist-list {
            list-style: none;
            padding: 0;
            margin: 0;
        }
        
        .artist-list li {
            padding: 8px 0;
            border-bottom: 1px solid #8d99ae;
            overflow: hidden;
        }
        
        .artist-count {
            background: #4361ee;
            color: white;
            padding: 2px 8px;
            border-radius: 10px;
            font-size: 0.9rem;
            float: right;
        }
        
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
        }
        
        th, td {
            padding: 10px;
            text-align: left;
            border-bottom: 1px solid #8d99ae;
        }
        
        th {
            color: #4cc9f0;
            font-weight: 600;
        }
        
        .form-group {
            margin-bottom: 15px;
        }
        
        .form-group label {
            display: block;
            margin-bottom: 5px;
            color: #4cc9f0;
            font-weight: 500;
        }
        
        .form-group input {
            width: 100%;
            padding: 10px;
            border: 1px solid #8d99ae;
            background: #2b2d42;
            color: white;
            font-size: 1rem;
        }
        
        .alert {
            padding: 10px;
            margin-bottom: 15px;
        }
        
        .alert-success {
            background: #2ecc71;
            color: white;
        }
        
        .alert-error {
            background: #e74c3c;
            color: white;
        }
        
        footer {
            text-align: center;
            padding: 20px 0;
            color: #8d99ae;
            font-size: 0.9rem;
            clear: both;
        }
        
        .pagination {
            text-align: center;
            margin: 20px 0;
        }
        
        .pagination a, .pagination span {
            padding: 5px 10px;
            margin: 0 2px;
            text-decoration: none;
            color: white;
            background: #4361ee;
        }
        
        .pagination .current {
            background: #4cc9f0;
        }
        
        .clear {
            clear: both;
        }
    </style>
</head>
<body>
    <div class="container">
        <header>
            <div class="logo">
                <i class="fa fa-music"></i>
                <h1>音乐数据库管理系统</h1>
            </div>
            <div style="float: right;">
                <a href="?" class="btn btn-primary"><i class="fa fa-home"></i> 返回首页</a>
            </div>
            <div class="clear"></div>
        </header>
        
        <div style="margin-bottom: 20px;">
            <form method="GET" action="" style="float: left; width: 70%;">
                <input type="text" name="search" placeholder="搜索歌曲或歌手..." value="<?= htmlspecialchars($search) ?>" style="width: 80%; padding: 8px;">
                <button type="submit" style="padding: 8px 15px; background: #4361ee; color: white; border: none;">搜索</button>
            </form>
            <a href="?" class="btn btn-success" style="float: right;"><i class="fa fa-refresh"></i> 重置搜索</a>
            <div class="clear"></div>
        </div>
        
        <?php if (isset($success)): ?>
            <div class="alert alert-success">
                <i class="fa fa-check-circle"></i> <?= $success ?>
            </div>
        <?php endif; ?>
        
        <?php if (isset($error)): ?>
            <div class="alert alert-error">
                <i class="fa fa-exclamation-circle"></i> <?= $error ?>
            </div>
        <?php endif; ?>
        
        <div>
            <div class="card">
                <h3><i class="fa fa-headphones"></i> 歌曲总数</h3>
                <div class="stat"><?= $totalSongs ?></div>
                <p>数据库中的歌曲数量</p>
            </div>
            
            <div class="card">
                <h3><i class="fa fa-user"></i> 歌手统计</h3>
                <p>共有 <?= count($artists) ?> 位歌手</p>
                <ul class="artist-list">
                    <?php foreach ($artists as $artist): ?>
                        <li>
                            <span><?= htmlspecialchars($artist['artist']) ?></span>
                            <span class="artist-count"><?= $artist['count'] ?></span>
                        </li>
                    <?php endforeach; ?>
                </ul>
            </div>
            
            <div class="card" style="margin-right: 0;">
                <h3><i class="fa fa-crown"></i> 最受欢迎歌手</h3>
                <div class="stat"><?= $mostPopularArtist ? htmlspecialchars($mostPopularArtist['artist']) : '暂无数据' ?></div>
                <p>拥有 <?= $mostPopularArtist ? $mostPopularArtist['count'] : '0' ?> 首歌曲</p>
            </div>
            <div class="clear"></div>
        </div>
        
        <div style="float: left; width: 65%; margin-right: 5%;">
            <h2 style="color: #4cc9f0; margin-bottom: 15px;"><i class="fa fa-list"></i> 歌曲列表</h2>
            <p>共 <?= $totalSongs ?> 首歌曲</p>
            
            <?php if ($totalSongs > 0): ?>
                <table cellspacing="0" cellpadding="0" border="0">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>歌曲名称</th>
                            <th>歌手</th>
                            <th>专辑</th>
                            <th>MP3地址</th>
                            <th>操作</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($songs as $song): ?>
                            <tr>
                                <td><?= $song['id'] ?></td>
                                <td><?= htmlspecialchars($song['title']) ?></td>
                                <td><?= htmlspecialchars($song['artist']) ?></td>
                                <td><?= htmlspecialchars($song['album']) ?></td>
                                <td>
                                    <a href="<?= htmlspecialchars($song['mp3_url']) ?>" target="_blank">
                                        <i class="fa fa-external-link"></i> 访问链接
                                    </a>
                                </td>
                                <td>
                                    <a href="?edit=<?= $song['id'] ?>" class="btn btn-primary" style="padding: 5px 10px;">
                                        <i class="fa fa-edit"></i> 编辑
                                    </a>
                                    <a href="?delete=<?= $song['id'] ?>" class="btn btn-danger" style="padding: 5px 10px;" onclick="return confirm('确定要删除这首歌曲吗？')">
                                        <i class="fa fa-trash"></i> 删除
                                    </a>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                
                <div class="pagination">
                    <?php if ($page > 1): ?>
                        <a href="?page=1">&laquo;</a>
                        <a href="?page=<?= $page-1 ?>">&lsaquo;</a>
                    <?php endif; ?>
                    
                    <?php for ($i = 1; $i <= $totalPages; $i++): ?>
                        <?php if ($i == $page): ?>
                            <span class="current"><?= $i ?></span>
                        <?php else: ?>
                            <a href="?page=<?= $i ?>"><?= $i ?></a>
                        <?php endif; ?>
                    <?php endfor; ?>
                    
                    <?php if ($page < $totalPages): ?>
                        <a href="?page=<?= $page+1 ?>">&rsaquo;</a>
                        <a href="?page=<?= $totalPages ?>">&raquo;</a>
                    <?php endif; ?>
                </div>
            <?php else: ?>
                <div style="text-align: center; padding: 50px 0;">
                    <i class="fa fa-music" style="font-size: 4rem; color: #8d99ae;"></i>
                    <h3>没有找到歌曲</h3>
                    <p>数据库中没有歌曲数据，请添加新歌曲</p>
                </div>
            <?php endif; ?>
        </div>
        
        <div style="float: left; width: 30%;">
            <form method="POST" action="">
                <?php if ($edit_song): ?>
                    <input type="hidden" name="id" value="<?= $edit_song['id'] ?>">
                <?php endif; ?>
                
                <h2 style="color: #4cc9f0; margin-bottom: 15px;">
                    <i class="fa <?= $edit_song ? 'fa-save' : 'fa-plus' ?>"></i>
                    <?= $edit_song ? '编辑歌曲' : '添加新歌曲' ?>
                </h2>
                
                <div class="form-group">
                    <label for="title">歌曲名称 *</label>
                    <input type="text" id="title" name="title" value="<?= $edit_song ? htmlspecialchars($edit_song['title']) : '' ?>" required>
                </div>
                
                <div class="form-group">
                    <label for="artist">歌手 *</label>
                    <input type="text" id="artist" name="artist" value="<?= $edit_song ? htmlspecialchars($edit_song['artist']) : '' ?>" required>
                </div>
                
                <div class="form-group">
                    <label for="album">专辑 *</label>
                    <input type="text" id="album" name="album" value="<?= $edit_song ? htmlspecialchars($edit_song['album']) : '' ?>" required>
                </div>
                
                <div class="form-group">
                    <label for="mp3_url">MP3地址 *</label>
                    <input type="text" id="mp3_url" name="mp3_url" value="<?= $edit_song ? htmlspecialchars($edit_song['mp3_url']) : '' ?>" placeholder="http://example.com/song.mp3" required>
                </div>
                
                <div class="form-group">
                    <button type="submit" name="<?= $edit_song ? 'update_song' : 'add_song' ?>" class="btn btn-success" style="width: 100%;">
                        <i class="fa <?= $edit_song ? 'fa-save' : 'fa-plus' ?>"></i>
                        <?= $edit_song ? '更新歌曲信息' : '添加新歌曲' ?>
                    </button>
                    
                    <?php if ($edit_song): ?>
                        <a href="?" class="btn btn-primary" style="width: 100%; margin-top: 10px;">
                            <i class="fa fa-times"></i> 取消编辑
                        </a>
                    <?php endif; ?>
                </div>
            </form>
        </div>
        <div class="clear"></div>
        
        <footer>
            <p>音乐数据库管理系统 &copy; <?= date('Y') ?> | 基于PHP和MySQL构建</p>
        </footer>
    </div>
</body>
</html>